package com.ws.shop.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

import org.apache.log4j.Logger;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.stereotype.Repository;

import com.ws.shop.bean.Page;
import com.ws.shop.dao.OrderDao;
import com.ws.shop.entity.Address;
import com.ws.shop.entity.Order;
import com.ws.shop.util.JdbcUtil;

@Repository(value = "orderDao")
public class OrderDaoImpl extends BaseDaoImpl implements OrderDao {
	private static Logger log = Logger.getLogger(OrderDaoImpl.class);
	final String findOrderListPage_count = "select count(1) from jvf_order";
	final String findOrderListPage_list = "select id,uid,ordersn,aid,goodsname,num,orderprice,distributionway,freight,name,recipient,address,postalcode,tel,remark,type,isrebeat,addtime,state from jvf_order";
	final String findOrderListPage_order = " order by id desc";

	public Page findOrderListPage(long pageno, int pagesize, String name,
			String start, String end, int aid, int type) {
		StringBuilder query = new StringBuilder(" where 1=1");
		if (name != null)
			query.append(" and name='").append(name).append("'");
		if (end != null)
			query.append(" and addtime<='").append(end).append(" 23:59:59")
					.append("'");
		if (start != null)
			query.append(" and addtime>='").append(start).append(" 00:00:00")
					.append("'");
		if (aid > 0)
			query.append(" and aid=").append(aid);
		if (type >= 0)
			query.append(" and type=").append(type);
		return findPage(pageno, pagesize, new StringBuilder(
				findOrderListPage_count).append(query).toString(),
				new StringBuilder(findOrderListPage_list).append(query).append(
						findOrderListPage_order));
	}

	final String updateOrderState = "update jvf_order set state=? where id=?";

	public void updateOrderState(int id, int state) {
		this.update(updateOrderState, state, id);
	}

	final String findOrderById = "select uid,ordersn,aid,goodsname,num,orderprice,distributionway,freight,name,recipient,address,postalcode,tel,remark,type,isrebeat,addtime,state from jvf_order where id=?";

	public Order findOrderById(final int id) {
		return this.execute(findOrderById,
				new PreparedStatementCallback<Order>() {
					public Order doInPreparedStatement(PreparedStatement ps)
							throws SQLException, DataAccessException {
						ResultSet rs = null;
						try {
							ps.setInt(1, id);
							rs = ps.executeQuery();
							if (rs.next()) {
								Order o = new Order();
								o.setId(id);
								o.setUid(rs.getInt(1));
								o.setOrdersn(rs.getString(2));
								o.setAid(rs.getInt(3));
								o.setGoodsname(rs.getString(4));
								o.setNum(rs.getInt(5));
								o.setOrderprice(rs.getFloat(6));
								o.setDistributionway(rs.getString(7));
								o.setFreight(rs.getFloat(8));
								o.setName(rs.getString(9));
								o.setRecipient(rs.getString(10));
								o.setAddress(rs.getString(11));
								o.setPostalcode(rs.getString(12));
								o.setTel(rs.getString(13));
								o.setRemark(rs.getString(14));
								o.setType(rs.getInt(15));
								o.setIsrebeat(rs.getInt(16));
								o.setAddtime(rs.getTimestamp(17));
								o.setState(rs.getInt(18));
								return o;
							}
							return null;
						} finally {
							JdbcUtil.closeResultSet(rs);
						}
					}
				});
	}

	public List<Order> getOrderList(final int type, final long aid,
			final int state) {
		StringBuffer sbuffer = new StringBuffer();
		sbuffer
				.append("SELECT O.id,O.uid,O.ordersn,O.aid,O.goodsname,O.num,O.orderprice,O.distributionway,O.freight,O.name,O.recipient,O.address,O.postalcode,O.tel,O.remark,O.type,O.isrebeat,O.addtime,O.state ");
		sbuffer
				.append("FROM jvf_order AS O JOIN jvf_auction  AS A ON O.aid = A.id  JOIN jvf_goods AS G ON A.gid = G.id ");
		sbuffer
				.append("JOIN jvf_accessory_relation AS AR ON G.id = AR.relationid AND AR.tables='Goods' JOIN jvf_accessory AS AC ON AR.accessoryid = AC.id ");
		sbuffer.append("WHERE O.type = " + type + " AND O.aid = " + aid
				+ " AND O.state> " + state);
		return this.query(sbuffer.toString(),
				new ResultSetExtractor<List<Order>>() {
					public List<Order> extractData(ResultSet rs)
							throws SQLException, DataAccessException {
						List<Order> list = new LinkedList<Order>();
						while (rs.next()) {
							Order o = new Order();
							o.setId(rs.getInt(1));
							o.setUid(rs.getInt(2));
							o.setOrdersn(rs.getString(3));
							o.setAid(rs.getInt(4));
							o.setGoodsname(rs.getString(5));
							o.setNum(rs.getInt(6));
							o.setOrderprice(rs.getFloat(7));
							o.setDistributionway(rs.getString(8));
							o.setFreight(rs.getFloat(9));
							o.setName(rs.getString(10));
							o.setRecipient(rs.getString(11));
							o.setAddress(rs.getString(12));
							o.setPostalcode(rs.getString(13));
							o.setTel(rs.getString(14));
							o.setRemark(rs.getString(15));
							o.setType(rs.getInt(16));
							o.setIsrebeat(rs.getInt(17));
							o.setAddtime(rs.getTimestamp(18));
							o.setState(rs.getInt(19));
							list.add(o);
						}
						return list;
					}
				});
	}

	public void mallorder(final Page page, final int type, final long uid) {
		final StringBuffer sbuffer = new StringBuffer();
		sbuffer
				.append("SELECT O.id,O.uid,O.ordersn,O.aid,O.goodsname,O.num,O.orderprice,O.distributionway,O.freight,O.name,O.recipient,O.address,O.postalcode,O.tel,O.remark,O.type,O.isrebeat,O.addtime,O.state,A.title,G.goodsfreight,E.number,S.id as sid ");
		sbuffer
				.append("FROM jvf_order AS O JOIN jvf_mall  AS A ON O.aid = A.id  JOIN jvf_goods AS G ON A.gid = G.id ");
		sbuffer
				.append("JOIN jvf_express as E ON O.id = E.oid JOIN jvf_sunpic as S ON O.id = S.oid order by O.id desc ");
		sbuffer.append("WHERE  O.uid = " + uid + " and O.type = " + type);
		this.execute(new ConnectionCallback() {
			@SuppressWarnings("unchecked")
			public Object doInConnection(Connection conn) throws SQLException,
					DataAccessException {
				Statement stmt = null;
				ResultSet rs = null;
				try {
					String s = "SELECT COUNT(1) FROM jvf_order WHERE uid = "
							+ uid + " AND TYPE = " + type;
					stmt = conn.createStatement();
					rs = stmt.executeQuery(s);
					long totalnum = 0;
					if (rs.next()) {
						totalnum = rs.getLong(1);
						page.setTotalnum(totalnum);
					}
					JdbcUtil.close(rs, stmt, null);
					if (totalnum > 0) {
						rs = stmt.executeQuery(sbuffer.append(" limit ")
								.append(page.getStartrownum()).append(",")
								.append(page.getPagesize()).toString());
						while (rs.next()) {
							Order order = new Order();
							Order o = new Order();
							o.setId(rs.getInt(1));
							o.setUid(rs.getInt(2));
							o.setOrdersn(rs.getString(3));
							o.setAid(rs.getInt(4));
							o.setGoodsname(rs.getString(5));
							o.setNum(rs.getInt(6));
							o.setOrderprice(rs.getFloat(7));
							o.setDistributionway(rs.getString(8));
							o.setFreight(rs.getFloat(9));
							o.setName(rs.getString(10));
							o.setRecipient(rs.getString(11));
							o.setAddress(rs.getString(12));
							o.setPostalcode(rs.getString(13));
							o.setTel(rs.getString(14));
							o.setRemark(rs.getString(15));
							o.setType(rs.getInt(16));
							o.setIsrebeat(rs.getInt(17));
							o.setAddtime(rs.getTimestamp(18));
							o.setState(rs.getInt(19));
							o.setAtitle(rs.getString(20));
							o.setGgoodsfreight(rs.getDouble(21));
							o.setEnumber(rs.getString(22));
							o.setSid(rs.getLong(23));
							page.addData(order);
						}
					}
				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					JdbcUtil.close(rs, stmt, null);
				}
				return null;
			}
		});

	}

	// 支付或者未支付订单
	public void orderPayment(final Page page, final int type, final long uid) {
		final StringBuffer sbuffer = new StringBuffer();
		sbuffer
				.append("SELECT O.id,O.uid,O.ordersn,O.aid,O.goodsname,O.num,O.orderprice,O.distributionway,O.freight,O.name,O.recipient,O.address,O.postalcode,O.tel,O.remark,O.type,O.isrebeat,O.addtime,O.state,A.sn,A.ordertime,A.realprice,G.goodsfreight,E.number,S.id as sid ");
		sbuffer
				.append("FROM jvf_order AS O JOIN jvf_auction  AS A ON O.aid = A.id  JOIN jvf_goods AS G ON A.gid = G.id ");
		sbuffer
				.append("JOIN jvf_express as E ON O.id = E.oid JOIN jvf_sunpic as S ON O.id = S.oid order by O.id desc ");
		sbuffer.append("WHERE O.uid = " + uid + " type = " + type + " and ");
		if (type == 0) {
			sbuffer.append(" O.state = 0");
		} else {
			sbuffer.append(" O.state between 1 and 4 ");
		}
		this.execute(new ConnectionCallback() {
			@SuppressWarnings("unchecked")
			public Object doInConnection(Connection conn) throws SQLException,
					DataAccessException {
				Statement stmt = null;
				ResultSet rs = null;
				try {
					String s = "SELECT COUNT(1) FROM jvf_order WHERE uid = "
							+ uid + " and  type =  " + type;
					if (type == 0) {
						s = s + " and state = 0 ";
					} else {
						s = s + " and state between 1 and 4 ";
					}
					stmt = conn.createStatement();
					rs = stmt.executeQuery(s);
					long totalnum = 0;
					if (rs.next()) {
						totalnum = rs.getLong(1);
						page.setTotalnum(totalnum);
					}
					JdbcUtil.close(rs, stmt, null);
					if (totalnum > 0) {

						rs = stmt.executeQuery(sbuffer.append(" limit ")
								.append(page.getStartrownum()).append(",")
								.append(page.getPagesize()).toString());
						while (rs.next()) {
							Order order = new Order();
							Order o = new Order();
							o.setId(rs.getInt(1));
							o.setUid(rs.getInt(2));
							o.setOrdersn(rs.getString(3));
							o.setAid(rs.getInt(4));
							o.setGoodsname(rs.getString(5));
							o.setNum(rs.getInt(6));
							o.setOrderprice(rs.getFloat(7));
							o.setDistributionway(rs.getString(8));
							o.setFreight(rs.getFloat(9));
							o.setName(rs.getString(10));
							o.setRecipient(rs.getString(11));
							o.setAddress(rs.getString(12));
							o.setPostalcode(rs.getString(13));
							o.setTel(rs.getString(14));
							o.setRemark(rs.getString(15));
							o.setType(rs.getInt(16));
							o.setIsrebeat(rs.getInt(17));
							o.setAddtime(rs.getTimestamp(18));
							o.setState(rs.getInt(19));
							o.setSn(rs.getString(20));
							o.setAordertime(rs.getTimestamp(21));
							o.setRealprice(rs.getDouble(22));
							o.setGgoodsfreight(rs.getDouble(23));
							o.setEnumber(rs.getString(24));
							o.setSid(rs.getLong(25));
							page.addData(order);
						}
					}
				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					JdbcUtil.close(rs, stmt, null);
				}
				return null;
			}
		});
	}

	public int clinch(final long uid, final long oid) {
		return (Integer) this.execute(new ConnectionCallback() {
			@SuppressWarnings("unchecked")
			public Object doInConnection(Connection connect)
					throws SQLException, DataAccessException {
				Statement stmt = null;
				ResultSet rs = null;
				try {
					connect.setAutoCommit(false);
					String s = "SELECT uid,state FROM jvf_order WHERE id = "
							+ oid + " for update";
					stmt = connect.createStatement();
					rs = stmt.executeQuery(s);
					if (rs.next()) {
						long userid = rs.getLong(1);
						int state = rs.getInt(2);
						if (uid == userid) {
							if (state != 2) {
								connect.rollback();
								return 3;// 订单状态不正确
							} else {
								JdbcUtil.close(rs, stmt, null);
								s = "update jvf_order set state = 3 where id = "
										+ oid;
								stmt = connect.createStatement();
								int c = stmt.executeUpdate(s);
								if (c > 0) {
									connect.commit();
									return 0;
								} else {
									connect.rollback();
									return 4;
								}
							}

						} else {
							connect.rollback();
							return 2;// 非法操作
						}

					} else {
						connect.rollback();
						return 1;// 订单不存在
					}
				} catch (Exception e) {
					e.printStackTrace();
					connect.rollback();
					log.error(e.getMessage(), e);
					return -1;
				} finally {
					JdbcUtil.close(rs, stmt, null);
				}
			}
		});
	}

	public void loseOrder(final Page page, final int state, final long uid) {
		final StringBuffer sbuffer = new StringBuffer();
		sbuffer
				.append("SELECT O.id,O.uid,O.ordersn,O.aid,O.goodsname,O.num,O.orderprice,O.distributionway,O.freight,O.name,O.recipient,O.address,O.postalcode,O.tel,O.remark,O.type,O.isrebeat,O.addtime,O.state,A.sn,A.goodsprice,A.ordertime ");
		sbuffer
				.append("FROM jvf_order as O JOIN jvf_auction as A ON O.aid = A.id");
		sbuffer.append("WHERE O.uid = " + uid + " O.state = " + state
				+ " order by O.id desc");
		this.execute(new ConnectionCallback() {
			@SuppressWarnings("unchecked")
			public Object doInConnection(Connection conn) throws SQLException,
					DataAccessException {
				Statement stmt = null;
				ResultSet rs = null;
				try {
					String s = "SELECT COUNT(1) FROM jvf_order WHERE uid = "
							+ uid + " and  state =  " + state;

					stmt = conn.createStatement();
					rs = stmt.executeQuery(s);
					long totalnum = 0;
					if (rs.next()) {
						totalnum = rs.getLong(1);
						page.setTotalnum(totalnum);
					}
					JdbcUtil.close(rs, stmt, null);
					if (totalnum > 0) {

						rs = stmt.executeQuery(sbuffer.append(" limit ")
								.append(page.getStartrownum()).append(",")
								.append(page.getPagesize()).toString());
						while (rs.next()) {
							Order order = new Order();
							Order o = new Order();
							o.setId(rs.getInt(1));
							o.setUid(rs.getInt(2));
							o.setOrdersn(rs.getString(3));
							o.setAid(rs.getInt(4));
							o.setGoodsname(rs.getString(5));
							o.setNum(rs.getInt(6));
							o.setOrderprice(rs.getFloat(7));
							o.setDistributionway(rs.getString(8));
							o.setFreight(rs.getFloat(9));
							o.setName(rs.getString(10));
							o.setRecipient(rs.getString(11));
							o.setAddress(rs.getString(12));
							o.setPostalcode(rs.getString(13));
							o.setTel(rs.getString(14));
							o.setRemark(rs.getString(15));
							o.setType(rs.getInt(16));
							o.setIsrebeat(rs.getInt(17));
							o.setAddtime(rs.getTimestamp(18));
							o.setState(rs.getInt(19));
							o.setSn(rs.getString(20));
							o.setGoodsprice(rs.getDouble(21));
							o.setAordertime(rs.getTimestamp(22));
							page.addData(order);
						}
					}
				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					JdbcUtil.close(rs, stmt, null);
				}
				return null;
			}
		});
	}

	public Order getOne(final long uid, final long id, final int state,final int type ) {
		final StringBuffer sbuffer = new StringBuffer();
		sbuffer
				.append("SELECT O.id,O.uid,O.ordersn,O.aid,O.goodsname,O.num,O.orderprice,O.distributionway,O.freight,O.name,O.recipient,O.address,O.postalcode,O.tel,O.remark,O.type,O.isrebeat,O.addtime,O.state,A.sn,A.ordertime,A.realprice,A.goodsprice,G.goodsfreight,AC.thumbnail ");
		sbuffer
				.append("FROM jvf_order AS O JOIN jvf_auction  AS A ON O.aid = A.id  JOIN jvf_goods AS G ON A.gid = G.id ");
		sbuffer
				.append("JOIN jvf_accessory_relation as AR ON G.id = AR.relationid and AR.tables='Goods' JOIN jvf_accessory as AC on AR.accessoryid = AC.id ");
		if(type==1){
			sbuffer.append("WHERE O.id = ? and O.uid = ?  and O.state = ? ");
		}else if(type == 2){
			sbuffer.append("WHERE O.id = ? ");
		}


		return this.execute(sbuffer.toString(),
				new PreparedStatementCallback<Order>() {
					public Order doInPreparedStatement(PreparedStatement ps)
							throws SQLException, DataAccessException {
						ResultSet rs = null;
						try {
							ps.setLong(1, id);
							ps.setLong(2, uid);
							ps.setInt(3, state);
							rs = ps.executeQuery();
							if (rs.next()) {
								Order order = new Order();
								Order o = new Order();
								o.setId(rs.getInt(1));
								o.setUid(rs.getInt(2));
								o.setOrdersn(rs.getString(3));
								o.setAid(rs.getInt(4));
								o.setGoodsname(rs.getString(5));
								o.setNum(rs.getInt(6));
								o.setOrderprice(rs.getFloat(7));
								o.setDistributionway(rs.getString(8));
								o.setFreight(rs.getFloat(9));
								o.setName(rs.getString(10));
								o.setRecipient(rs.getString(11));
								o.setAddress(rs.getString(12));
								o.setPostalcode(rs.getString(13));
								o.setTel(rs.getString(14));
								o.setRemark(rs.getString(15));
								o.setType(rs.getInt(16));
								o.setIsrebeat(rs.getInt(17));
								o.setAddtime(rs.getTimestamp(18));
								o.setState(rs.getInt(19));
								o.setSn(rs.getString(20));
								o.setAordertime(rs.getTimestamp(21));
								o.setRealprice(rs.getDouble(22));
								o.setGoodsprice(rs.getDouble(23));
								o.setGgoodsfreight(rs.getDouble(24));
								o.setThumbnail(rs.getString(25));
								return order;
							}
							return null;
						} finally {
							JdbcUtil.closeResultSet(rs);
						}
					}
				});

	}

	public int updateOrder(final Order order){
		return (Integer)this.execute(
				new ConnectionCallback(){
					@SuppressWarnings("unchecked")
					public Object doInConnection(Connection connect)
					throws SQLException, DataAccessException {
						Statement stmt = null;
						ResultSet rs = null;
						try{
							connect.setAutoCommit(false);
							String s = "select id from  jvf_order where id  = "+order.getId()+" and uid = "+order.getUid()+" and state = "+order.getState()+"  for update" ;
							stmt = connect.createStatement();
							rs = stmt.executeQuery(s);
							if(rs.next()){
								 s = " update jvf_order set recipient =? ,tel=?,postalcode=?,address=?,remark = ? where id = ?";
								 PreparedStatement ps = connect.prepareStatement(s);
								 ps.setString(1, order.getRecipient());
								 ps.setString(2, order.getTel());
								 ps.setString(3, order.getPostalcode());
								 ps.setString(4, order.getAddress());
								 ps.setString(5, order.getRemark());
								 ps.setLong(6, order.getId());
								 int c = ps.executeUpdate();
								 JdbcUtil.close(null, ps, null);
								if(c>0){
									connect.commit();
									return 0;
								}else{
									connect.rollback();
									return 2;//修改失败
								}

							}else{
								connect.rollback();
								return 1;// 不存在
							}
						}catch (Exception e) {
							e.printStackTrace();
							connect.rollback();
							log.error(e.getMessage(), e);
							return  -1;
						} finally {
							JdbcUtil.close(rs, stmt, null);
						}
					}
				}
		);
	}
	
	
}
